//
//  DataBaseHandle.m
//  A段项目,漫画
//
//  Created by dllo on 15/4/24.
//  Copyright (c) 2015年 dllo. All rights reserved.
//

#import "DataBaseHandle.h"

@implementation DataBaseHandle

//单例方法
+ (instancetype)shareDB{
    static DataBaseHandle *dataBase = nil;
    static dispatch_once_t oneToTake;
    dispatch_once(&oneToTake, ^{
        dataBase = [[DataBaseHandle alloc] init];
    });
    return dataBase;
}
//打开数据库
- (void)openDB{
    //获取沙盒路径
    NSArray *sandBox = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *sanBoxPath = [sandBox lastObject];
    //拼接数据库路径
    NSString *dbPath = [sanBoxPath stringByAppendingPathComponent:@"saveCartoon.sqlite"];
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"开启数据库成功");
        NSLog(@"%@",dbPath);
    }else{
        NSLog(@"开启数据库失败");
    }
}
//创建表
- (void)createCartoonTable{
    //sql语句
    NSString *sqlStr = @"create table if not exists saveCartoon(comic_id text, picURL text, title text, number integer primary key autoincrement)";
    //执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建动漫表成功");
    }else{
        NSLog(@"创建动漫表失败");
    }
}
//创建浏览记录表
- (void)createThroughTable{
    //sql语句
    NSString *sqlStr = @"create table if not exists throughNotes(comic_id text primary key, cid text, picURL text, title text, lastPage integer)";
    //执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建浏览记录表成功");
    }else{
        NSLog(@"创建浏览记录表失败");
    }
}
//插入动漫
- (void)insertCartoonWithID:(NSString *)comic_id PicUrl:(NSString *)url AndTiTle:(NSString *)title{
    NSString *insertStu = [NSString stringWithFormat:@"insert into saveCartoon(comic_id, picURL, title) values('%@', '%@', '%@')", comic_id, url, title];
    int result = sqlite3_exec(dbPoint, [insertStu UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}
//插入浏览记录
- (void)insertThroughNotesWithID:(NSString *)comic_id Cid:(NSString *)cid PicUrl:(NSString *)url TiTle:(NSString *)title AndLastPage:(NSInteger)index{
    NSString *insertStu = [NSString stringWithFormat:@"insert into throughNotes(comic_id, cid, picURL, title, lastPage) values('%@', '%@', '%@', '%@', '%ld')", comic_id, cid, url, title, index];
    int result = sqlite3_exec(dbPoint, [insertStu UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}


//查询动漫
- (NSMutableArray *)selectSaveCartoon{
    NSString *selectStr = [NSString stringWithFormat:@"select * from saveCartoon"];
    //需要创建一个跟随指针指,用来循环遍历数据库中表的每行数据
    sqlite3_stmt *stmt = nil;
    //第三个参数:查询语句字数限制,-1为不限制
    int result = sqlite3_prepare_v2(dbPoint, [selectStr UTF8String], -1, &stmt, nil);
    //这个方法相当于数据库,sql,跟随指针关联在一起,一同完成查询操作
    //初始化装学生的大数组
    NSMutableArray *comic_idArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询动漫成功");
        //开始遍历查询的数据库的每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //放跟随指针以行进行查询,遍历,如果没有行,则停止循环
            //满足循环条件,则逐列取出内容
            const unsigned char *comic_id = sqlite3_column_text(stmt, 0);
            const unsigned char *picURL = sqlite3_column_text(stmt, 1);
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            //把取出来的内容进行类型的转换
            NSString *comicid = [NSString stringWithUTF8String:(const char*)comic_id];
            NSString *pic = [NSString stringWithUTF8String:(const char*)picURL];
            NSString *titleName = [NSString stringWithUTF8String:(const char*)title];
            //给对象进行赋值
            saveCartoon *save = [[saveCartoon alloc] init];
            save.comic_id = comicid;
            save.picURL = pic;
            save.title = titleName;
            [comic_idArr addObject:save];
        }
    }else{
        NSLog(@"查询动漫失败");
    }
    return comic_idArr;
}
//查询浏览记录
- (NSMutableArray *)selectThroughNotes{
    NSString *selectStr = [NSString stringWithFormat:@"select * from throughNotes"];
    //需要创建一个跟随指针指,用来循环遍历数据库中表的每行数据
    sqlite3_stmt *stmt = nil;
    //第三个参数:查询语句字数限制,-1为不限制
    int result = sqlite3_prepare_v2(dbPoint, [selectStr UTF8String], -1, &stmt, nil);
    //这个方法相当于数据库,sql,跟随指针关联在一起,一同完成查询操作
    //初始化装学生的大数组
    NSMutableArray *throughNotesArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询浏览记录成功");
        //开始遍历查询的数据库的每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //放跟随指针以行进行查询,遍历,如果没有行,则停止循环
            //满足循环条件,则逐列取出内容
//            comic_id, cid, picURL, title, lastPage
            const unsigned char *comic_id = sqlite3_column_text(stmt, 0);
            const unsigned char *cid = sqlite3_column_text(stmt, 1);
            const unsigned char *picURL = sqlite3_column_text(stmt, 2);
            const unsigned char *title = sqlite3_column_text(stmt, 3);
            int lastPage = sqlite3_column_int(stmt, 4);
            //把取出来的内容进行类型的转换
            NSString *comicid = [NSString stringWithUTF8String:(const char*)comic_id];
            NSString *cid1 = [NSString stringWithUTF8String:(const char*)cid];
            NSString *picURL1 = [NSString stringWithUTF8String:(const char*)picURL];
            NSString *title1 = [NSString stringWithUTF8String:(const char*)title];
            NSInteger lastPage1 = lastPage;
            //给对象进行赋值
            throughNotes *through = [[throughNotes alloc] init];
            through.comic_id = comicid;
            through.cid = cid1;
            through.picURL = picURL1;
            through.title = title1;
            through.lastPage = lastPage1;
            [throughNotesArr addObject:through];
        }
    }else{
        NSLog(@"查询浏览记录失败");
    }
    return throughNotesArr;
}


//删除动漫数据
- (void)deleteCartoonWithID:(NSString *)comic_id{
    NSString *deleteStr = [NSString stringWithFormat:@"delete from saveCartoon where comic_id = %@",comic_id];
    //执行语句
    int result = sqlite3_exec(dbPoint, [deleteStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }

}
//删除浏览记录数据
- (void)deleteThroughNotesWithID:(NSString *)comic_id{
    NSString *deleteStr = [NSString stringWithFormat:@"delete from throughNotes where comic_id = %@",comic_id];
    //执行语句
    int result = sqlite3_exec(dbPoint, [deleteStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}
//删除动漫数据库的表
- (void)dropCartoonTable{
    NSString *dropStr = [NSString stringWithFormat:@"drop table saveCartoon"];
    int result = sqlite3_exec(dbPoint, [dropStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除动漫表成功");
    }else{
        NSLog(@"删除动漫表失败");
    }
}
//删除浏览记录表
- (void)dropThroughNotesTable{
    NSString *dropStr = [NSString stringWithFormat:@"drop table throughNotes"];
    int result = sqlite3_exec(dbPoint, [dropStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除浏览记录表成功");
    }else{
        NSLog(@"删除浏览记录表失败");
    }
}
//关闭数据库
- (void)closeDB{
    int result = sqlite3_close(dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"关闭数据库成功");
    }else{
        NSLog(@"关闭数据库失败");
    }

}
//更新浏览记录数据
- (void)upDateCid:(NSString *)cid ThroughNotesLastPage:(NSInteger)index ComicID:(NSString *)comicid{
    //主键不能修改
    NSString *updateStr = [NSString stringWithFormat:@"update throughNotes set cid = '%@', lastPage = '%ld' where comic_id = '%@'",cid, index, comicid];
    //执行语句
    int result = sqlite3_exec(dbPoint, [updateStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"更新浏览记录成功");
    }else{
        NSLog(@"更新浏览记录失败");
    }
}


@end
